Export a Microsoft Access report to PDF using Macro and email using Microsoft Outlook

This article is about to export the report to PDF using Macro and email this report using Microsoft Outlook. This approach is extremely useful to convert report into PDF format whenever we call the Macro.

For this purpose we have to make table and save data in table as shown in Fig 1.1.

And we have to select create Report from the Ribbon for current table. This will change data into report. Now we have to select the design view of the report and add two buttons, one is for save into report and second is for send report to Microsoft Outlook as shown in Fig 1.2.

There is another method also that is directly changing the report to PDF and email directly. We have just select the Email button from Ribbon and select the PDF format or any other format according to requirement. Then after pressing Ok button we are able to send email with PDF file as shown in Fig 1.3.

But we are interested with the help of macro, which is more interested technique. Using button on the report we are embedding the macro on event. After the selection of macro builder on “Save AS PDF” button the macro action window is opened .We has to select “Output To” action with object type report, and output format PDF format as shown in Fig 1.4.

After this we have to test to test 'save as PDF' button. Automatically this Report will change into PDF format as shown in Fig 1.5.

Next task is to send this PDF file to Microsoft Outlook as attachment .So on “send PDF” button we have to write VBA code. That will enable this to send to Outlook as shown in Fig 1.6.

VBA code for Send to Microsoft Outlook:

Option Compare Database

Private Sub sendemail_Click()
mailPDFReport
End Sub

Private Function mailPDFReport()
Dim MyOutlook As New Outlook.Application
Dim item As Outlook.MailItem
Dim sReport As String
Dim namePath As String
Dim fileName As String
Dim rpt As Access.Report

'Subjectline$ = InputBox$("Please enter the subject line for this mailing.", "We Need A Subject Line!")
sPath = Application.CurrentProject.Path & "\"
sReport = "Table1"
fileName = "Table1.pdf"
'BodyFile$ = InputBox$("Please enter the filename of the body of the message.", "We Need A Body!")
namePath = sPath & fileName
DoCmd.OutputTo objecttype:=acOutputReport, objectname:=sReport, outputformat:=acFormatPDF, outputfile:=namePath, autostart:=False
Set item = MyOutlook.CreateItem(mItem)
With item
item.To = "erpmakers@ark.com"
item.Subject = "EMail PDF"
item.Body = " "
item.Attachments.Add namePath
item.Display
End With
End Function